import pymysql

# TODO 数据库操作模块
# TODO 封装一个数据库操作类 DB
# TODO 读方法 read(sql) return 受影响行数,结果集
# TODO 写方法 wirte(sql) return 受影响行数
class DB:
    def __init__(self, database, host='localhost', user='root', password='root', port=3306, charset='utf8',
                 cursor_type=pymysql.cursors.Cursor):
        # 初始化连接对象
        self.__conn = pymysql.connect(host=host, user=user, password=password, port=port, charset=charset,
                                      database=database)
        # 初始化游标对象
        # 游标类型: 元组 字典
        self.__cursor = self.__conn.cursor(cursor_type)

    def read(self, sql, params_list=[]):
        try:
            # 执行sql语句
            rows = self.__cursor.execute(sql, params_list)
            # 获取结果集
            data = self.__cursor.fetchall()
        except Exception as e:
            print('sql语句输入有误')
            return None, 'NO data'
        return rows, data

    def write(self, sql):
        try:
            # 执行sql语句
            rows = self.__cursor.exectue(sql)
        except Exception as e:
            # 回滚
            self.__conn.rollback()
            return 0
        else:
            # 提交
            self.__conn.__commint()
        return rows

    def __del__(self):
        # 对象销毁后执行的方法
        self.__cursor.close()
        self.__conn.close()


if __name__ == '__main__':
    db = DB('advanced', cursor_type=pymysql.cursors.DictCursor)
    name = input('name:')
    rows, data = db.read(f'select * from student where stu_name = %s', [name])
    print(data)
    '''
    db = DB('advanced', cursor_type=pymysql.cursor.DictCursor)
    # 读操作
    rows, data = db.read('select * from student')
    if rows:
        print(rows, data)
    # 写操作
    rows = db.write("insert into student(stu_no, stu_name) values ('itsrc-017', '周瑜')")
    if rows:
    print('写入成功')
    '''
